package com.placement.refresh.dao;

import java.lang.reflect.Field;
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import javax.sql.DataSource;

//import oracle.sql.STRUCT;
//import oracle.sql.StructDescriptor;

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlReturnType;
import org.springframework.jdbc.core.support.AbstractSqlTypeValue;
import org.springframework.jdbc.object.StoredProcedure;

/**
 * @author Anirudha Joshi
 *   
 */
public class StoredProcHelperImpl extends StoredProcedure  {

	private DataSource dataSource;

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
		super.setDataSource(dataSource);
	}

	/**
	 * This is a generic method to execute Stored Procedures. This methos also
	 * can be used to execute SP's which takes Oracle Object Type(or Array) as
	 * IN/OUT parameter. Calling application would need to populate object(s) of
	 * type StoredProcVO to pass IN/OUT parameter information to this method.
	 * Here is a short description of what each field means.
	 * <li> paramName : Stored Procedure parameter name.
	 * <li> paramValue : Only applicable for IN parameters. If parameter is
	 * Oracle Object or ARRAY, you would need to pass Java Object instance.
	 * Please note that this Object class MUST implement java.sql.SQLData
	 * interface.
	 * <li> oracleTypeName : Oracle Object type name (which is defined in the
	 * DB)
	 * <li> paramValueClass : Class instance of java object. For ex: if you are
	 * passing ProposalBVO, class would be ProposalBVO.class.
	 * <li> sqlType : int value representing SP parameter mapping data type.
	 * (Use java.sql.Type)
	 * 
	 * Use below guidelines to populate StoredProcVO 1. If parameter data type
	 * is Oracle Object or Oracle Object Array, you MUST populate paramName,
	 * paramValue, oracleTypeName,paramValueClass and sqlType 2. If parameter
	 * data type is standard type (like varchar, numeric etc.) you should
	 * populate paramName,paramValue and sqlType 3. For OUT parameters, you dont
	 * need to populate <i>paramValue</i> field. 4. For Oracle Object, set
	 * sqlType as Types.STRUCT and for Oracle Object Array, set sqlType as
	 * Types.ARRAY
	 * 
	 * After executing stored procedure, this method returns a map whose KEY is
	 * OUT PARAM NAME and VALUE is populated Java Object / DB value returned by
	 * SP. If OUT param is Java Object then Calling application would need to
	 * cast java object instance to corresponding class. For ex: if calling appn
	 * is expecting a OUT param with name "user" and if OUT param Oracle Object
	 * type is represented as UserBVO, then this is how you should retrieve
	 * values. UserBVO user=(UserBVO)map.get("user");
	 * 
	 * If OUT param is Java Object Array, retriving values is slightly
	 * different. Calling application would first need to retrieve Object[] from
	 * the map and then cast each elemtn to corresponding object. For ex:
	 * Object[] arrUsers=(Object[])map.get("users"); for(int i=0;i<arrUsers.length;i++) {
	 * UserBVO user=(UserBVO)arrUsers[i]; }
	 * 
	 * @param procName :-
	 *            Stored Procedure Name
	 * @param inputParams :-
	 *            Array of StoredProvBVO. Each object would represet one IN
	 *            parameter
	 * @param outputParams :-
	 *            Array of StoredProvBVO. Each object would represet one OUT
	 *            parameter
	 * @return : Map containing value for each OUT param.
	 * @throws SQLException 
	 */

	public Map executeSP(String procName, StoredProcVO[] inputParams,
			StoredProcVO[] outputParams) throws SQLException {
		StoredProcVO provbvo = null;

		// TODO Check whether required parameters are passed or not.
//		if ((procName == null || procName.equals("") || (inputParams == null || inputParams.length == 0))) {
		if ((procName == null || procName.equals(""))) {
			// TODO
			// throw InsufficientArgumentException();
		}

		setSql(procName);
		
		/*
		 * Build input parameter details 1. Build param type info 2. set values
		 * in the hashmap to pass to SP
		 */
		HashMap inputMap = new HashMap();
		if(inputParams!=null){
			for (int i = 0; i < inputParams.length; i++) {
				provbvo = inputParams[i];
	
				if (provbvo.getSqlType() == Types.ARRAY)
					System.out.println("Not Implemented");
					//inputMap.put(provbvo.getParamName(), this
					//		.getInputTypeInfo(provbvo.getParamValue()));
				else
					inputMap.put(provbvo.getParamName(), provbvo.getParamValue());
	
				if (provbvo.getSqlType() == Types.ARRAY
						|| provbvo.getSqlType() == Types.STRUCT)
					declareParameter(new SqlParameter(provbvo.getParamName(),
							provbvo.getSqlType(), provbvo.getOracleTypeName()));
				else
					declareParameter(new SqlParameter(provbvo.getParamName(),
							provbvo.getSqlType()));
			}
		}

		// Build output parameter details
		if (outputParams != null) {
			for (int i = 0; i < outputParams.length; i++) {
				provbvo = outputParams[i];
				if (provbvo.getSqlType() == Types.ARRAY
						|| provbvo.getSqlType() == Types.STRUCT) {
					declareParameter(new SqlOutParameter(
							provbvo.getParamName(), provbvo.getSqlType(),
							provbvo.getOracleTypeName(),
							getSqlReturnType(provbvo)));
				} else
					declareParameter(new SqlOutParameter(
							provbvo.getParamName(), provbvo.getSqlType()));
			}
		}

		// Execute stored procedure by passing input values
		Map out = execute(inputMap);

		// Check whether any out parameter is Array type or not. If found,
		// convert it to specified class array
		this.convertArrayToObjectArray(outputParams, out);
		return out;
	}
	
	

	/**
	 * @param outputParams
	 * @param out
	 * @return
	 * @throws SQLException 
	 */
	private Map convertArrayToObjectArray(StoredProcVO[] outputParams, Map out) throws SQLException {
		StoredProcVO provbvo = null;
			if (outputParams != null && out != null)
				for (int i = 0; i < outputParams.length; i++) {
					provbvo = outputParams[i];
					if (provbvo.getSqlType() == Types.ARRAY) {
						Array retobjects = (Array) out.get(provbvo
								.getParamName());
						Object array = retobjects.getArray();
						Object[] objects = (Object[]) array;
						out.put(provbvo.getParamName(), objects);
					}
				}

		return out;

	}

	/**
	 * @param objectclass
	 * @return
	 */
	private SqlReturnType getSqlReturnType(final StoredProcVO procbvo) {
		return new SqlReturnType() {
			public Object getTypeValue(CallableStatement cs, int paramIndex,
					int sqlType, String typeName) throws SQLException {
				Connection con = cs.getConnection();
				Map typeMap = con.getTypeMap();
				HashMap oraTypeTojavaClass=procbvo.getOraTypeTojavaClass();
				if(oraTypeTojavaClass!=null && oraTypeTojavaClass.size()>0)
				{
					Iterator iter=oraTypeTojavaClass.keySet().iterator();
					String key;
					Class value;
					while(iter.hasNext())
					{
						key=(String)iter.next();
						value=(Class)oraTypeTojavaClass.get(key);
						typeMap.put(key, value);
					}
				}else
					typeMap.put(typeName, procbvo.getParamValueClass());
				
				Object o = cs.getObject(paramIndex);
				return o;
			}
		};
	}

	/**
	 * @param objects
	 * @return
	 */
/*	private AbstractSqlTypeValue getInputTypeInfo(final Object objects) {
		return new AbstractSqlTypeValue() {
			public Object createTypeValue(Connection con, int sqlType,
					String typeName) throws SQLException {
				Object obj=null;
				if(sqlType==Types.ARRAY){
					oracle.sql.ArrayDescriptor desc = new oracle.sql.ArrayDescriptor(
							typeName, con);
					obj=new oracle.sql.ARRAY(desc, con, objects);
				}else if(sqlType==Types.STRUCT){
					StructDescriptor desc = new oracle.sql.StructDescriptor(
							typeName, con);
					obj=new STRUCT(desc, con, (Object[])objects);
					con.getTypeMap().put("SRGDBO.PROPOSAL_TYPE",ProposalDTO.class);
					obj=objects;
				}
				return obj;
			}
		};
	}
*/
}
